package com.qjxt.qj.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qjxt.qj.pojo.Page;
import com.qjxt.qj.pojo.Sys_student;
import com.qjxt.qj.util.StringUtil;
import org.springframework.stereotype.Repository;


@Repository
public class StudentDao extends BaseDao {
	public boolean addStudent(Sys_student sysstudent){
		String sql = "insert into sys_student values(null,'"+ sysstudent.getSn()+"','"+ sysstudent.getName()+"'";
		sql += ",'" + sysstudent.getPassword() + "'," + sysstudent.getClazzId();
		sql += ",'" + sysstudent.getSex() + "','" + sysstudent.getMobile() + "'";
		sql += ",'" + sysstudent.getQq() + "',null)";
		return update(sql);
	}
	public boolean editStudent(Sys_student sysstudent) {
		// TODO Auto-generated method stub
		String sql = "update sys_student set name = '"+ sysstudent.getName()+"'";
		sql += ",sex = '" + sysstudent.getSex() + "'";
		sql += ",mobile = '" + sysstudent.getMobile() + "'";
		sql += ",qq = '" + sysstudent.getQq() + "'";
		sql += ",clazz_id = " + sysstudent.getClazzId();
		sql += " where id = " + sysstudent.getId();
		return update(sql);
	}
	public boolean setStudentPhoto(Sys_student sysstudent) {
		// TODO Auto-generated method stub
		String sql = "update s_student set photo = ? where id = ?";
		Connection connection = getConnection();
		try {
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setBinaryStream(1, sysstudent.getPhoto());
			prepareStatement.setInt(2, sysstudent.getId());
			return prepareStatement.executeUpdate() > 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return update(sql);
	}
	public boolean deleteStudent(String ids) {
		// TODO Auto-generated method stub
		String sql = "delete from sys_student where id in("+ids+")";
		return update(sql);
	}
	public Sys_student getStudent(int id){
		String sql = "select * from sys_student where id = " + id;
		Sys_student sysstudent = null;
		ResultSet resultSet = query(sql);
		try {
			if(resultSet.next()){
				sysstudent = new Sys_student();
				sysstudent.setId(resultSet.getInt("id"));
				sysstudent.setClazzId(resultSet.getInt("clazz_id"));
				sysstudent.setMobile(resultSet.getString("mobile"));
				sysstudent.setName(resultSet.getString("name"));
				sysstudent.setPassword(resultSet.getString("password"));
				sysstudent.setPhoto(resultSet.getBinaryStream("photo"));
				sysstudent.setQq(resultSet.getString("qq"));
				sysstudent.setSex(resultSet.getString("sex"));
				sysstudent.setSn(resultSet.getString("sn"));
				return sysstudent;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sysstudent;
	}
	public List<Sys_student> getStudentList(Sys_student sysstudent, Page sysPage){
		List<Sys_student> ret = new ArrayList<Sys_student>();
		String sql = "select * from sys_student ";
		if(!StringUtil.isEmpty(sysstudent.getName())){
			sql += "and name like '%" + sysstudent.getName() + "%'";
		}
		if(sysstudent.getClazzId() != 0){
			sql += " and clazz_id = " + sysstudent.getClazzId();
		}
		if(sysstudent.getId() != 0){
			sql += " and id = " + sysstudent.getId();
		}
		sql += " limit " + sysPage.getStart() + "," + sysPage.getPageSize();
		ResultSet resultSet = query(sql.replaceFirst("and", "where"));
		try {
			while(resultSet.next()){
				Sys_student s = new Sys_student();
				s.setId(resultSet.getInt("id"));
				s.setClazzId(resultSet.getInt("clazz_id"));
				s.setMobile(resultSet.getString("mobile"));
				s.setName(resultSet.getString("name"));
				s.setPassword(resultSet.getString("password"));
				s.setPhoto(resultSet.getBinaryStream("photo"));
				s.setQq(resultSet.getString("qq"));
				s.setSex(resultSet.getString("sex"));
				s.setSn(resultSet.getString("sn"));
				ret.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	public int getStudentListTotal(Sys_student sysstudent){
		int total = 0;
		String sql = "select count(*)as total from sys_student ";
		if(!StringUtil.isEmpty(sysstudent.getName())){
			sql += "and name like '%" + sysstudent.getName() + "%'";
		}
		if(sysstudent.getClazzId() != 0){
			sql += " and clazz_id = " + sysstudent.getClazzId();
		}
		if(sysstudent.getId() != 0){
			sql += " and id = " + sysstudent.getId();
		}
		ResultSet resultSet = query(sql.replaceFirst("and", "where"));
		try {
			while(resultSet.next()){
				total = resultSet.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	
	public Sys_student login(String name , String password){
		String sql = "select * from sys_student where name = '" + name + "' and password = '" + password + "'";
		ResultSet resultSet = query(sql);
		try {
			if(resultSet.next()){
				Sys_student sysstudent = new Sys_student();
				sysstudent.setId(resultSet.getInt("id"));
				sysstudent.setName(resultSet.getString("name"));
				sysstudent.setPassword(resultSet.getString("password"));
				sysstudent.setClazzId(resultSet.getInt("clazz_id"));
				sysstudent.setMobile(resultSet.getString("mobile"));
				sysstudent.setPhoto(resultSet.getBinaryStream("photo"));
				sysstudent.setQq(resultSet.getString("qq"));
				sysstudent.setSex(resultSet.getString("sex"));
				sysstudent.setSn(resultSet.getString("sn"));
				return sysstudent;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public boolean editPassword(Sys_student sysstudent, String newPassword) {
		// TODO Auto-generated method stub
		String sql = "update sys_student set password = '"+newPassword+"' where id = " + sysstudent.getId();
		return update(sql);
	}
}
